﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class GroupsDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoGroups model)
        {
            model.GRO_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Groups] ");
            sql.Append("([GRO_ID],[GRO_NAME],[GRO_CONTESTS],[GRO_AWARDS],[GRO_SPECIALTY],[GRO_IsShow],[GRO_TEACHER])");
            sql.Append(" VALUES");
            sql.Append(" (@GRO_ID,@GRO_NAME,@GRO_CONTESTS,@GRO_AWARDS,@GRO_SPECIALTY,@GRO_IsShow,@GRO_TEACHER)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoGroups model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Groups] SET ");
            sql.Append("GRO_NAME=@GRO_NAME");
            sql.Append(",GRO_CONTESTS=@GRO_CONTESTS");
            sql.Append(",GRO_AWARDS=@GRO_AWARDS");
            sql.Append(",GRO_SPECIALTY=@GRO_SPECIALTY");
            sql.Append(",GRO_IsShow=@GRO_IsShow");
            sql.Append(",GRO_TEACHER=@GRO_TEACHER");
            sql.Append(" WHERE ");
            sql.Append("GRO_ID=@GRO_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 根据竞赛的id来判断是否有此小组名称
        /// </summary>
        /// <param name="groupName">小组名称</param>
        /// <param name="ConId">竞赛id</param>
        /// <returns>返回bool类型</returns>
        public bool CheckGroupName(string groupName, string ConId)
        {
            StringBuilder sql = new StringBuilder();
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@GRO_NAME",groupName),
                new SqlParameter("@GRO_CONTESTS",ConId)
            };
            sql.Append("select count(GRO_NAME) from [Groups] where GRO_CONTESTS = @GRO_CONTESTS and  GRO_NAME = @GRO_NAME");
            return Convert.ToInt32(dbhelper.ExcuteQuery(sql.ToString(), parameter).Rows[0][0]) > 0;
        }
        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string gRO_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Groups] SET");
            sql.Append(" GRO_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("GRO_ID=@GRO_ID AND GRO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@GRO_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = gRO_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

         /// <summary>
        /// 根据条件来获得相应的数据的行数
        /// </summary>
        /// <param name="constr"></param>
        /// <returns></returns>
        public int getCountByCon(string constr)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(*) from Groups where 1=1 ");
            if (!string.IsNullOrEmpty(constr))
            {
                sql.Append(" and ");
                sql.Append(constr);
            }
            return dbhelper.ExcuteNoQuery(sql.ToString());
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoGroups GetModel(string gRO_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT GRO_ID,GRO_NAME,GRO_CONTESTS,GRO_AWARDS,GRO_SPECIALTY,GRO_IsShow,AWA_NAME,CON_NAME,GRO_TEACHER FROM Groups AS G LEFT JOIN Contests AS C ON G.GRO_CONTESTS=C.CON_ID LEFT JOIN Awards AS A ON G.GRO_AWARDS=A.AWA_ID");
            sql.Append(" WHERE ");
            sql.Append("GRO_ID=@GRO_ID AND GRO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@GRO_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = gRO_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoGroups> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoGroups> GetAllGroups()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT GRO_ID,GRO_NAME,GRO_CONTESTS,GRO_AWARDS,GRO_SPECIALTY,GRO_IsShow,AWA_NAME,CON_NAME,GRO_TEACHER FROM Groups AS G LEFT JOIN Contests AS C ON G.GRO_CONTESTS=C.CON_ID LEFT JOIN Awards AS A ON G.GRO_AWARDS=A.AWA_ID");
            sql.Append(" WHERE GRO_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoGroups> list = GetList(table);
            return list;
        }
        public IList<BoGroups> GetContestGroups(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT GRO_ID,GRO_NAME,GRO_CONTESTS,GRO_AWARDS,GRO_SPECIALTY,GRO_IsShow,AWA_NAME,CON_NAME,GRO_TEACHER FROM Groups AS G LEFT JOIN Contests AS C ON G.GRO_CONTESTS=C.CON_ID LEFT JOIN Awards AS A ON G.GRO_AWARDS=A.AWA_ID");
            sql.Append(" WHERE ");
            sql.Append("GRO_CONTESTS=@GRO_CONTESTS AND GRO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@GRO_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoGroups> list = GetList(table);
            return list;
        }

        public BoGroups GetGroupByName(string groupName, string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT GRO_ID,GRO_NAME,GRO_CONTESTS,GRO_AWARDS,GRO_SPECIALTY,GRO_IsShow,AWA_NAME,CON_NAME,GRO_TEACHER FROM Groups AS G LEFT JOIN Contests AS C ON G.GRO_CONTESTS=C.CON_ID LEFT JOIN Awards AS A ON G.GRO_AWARDS=A.AWA_ID");
            sql.Append(" WHERE ");
            sql.Append("GRO_CONTESTS=@GRO_CONTESTS AND GRO_NAME=@GRO_NAME AND GRO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[2];
            parameter[0] = new SqlParameter("@GRO_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            parameter[1] = new SqlParameter("@GRO_NAME", SqlDbType.NVarChar, 50);
            parameter[1].Value = groupName;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoGroups> list = GetList(table);
            return (list == null || list.Count == 0) ? null : list[0];
        }

        private IList<BoGroups> GetList(DataTable table)
        {
            List<BoGroups> list = new List<BoGroups>();
            if (table != null && table.Rows.Count > 0)
            {
                
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoGroups model = new BoGroups();
                    model.GRO_ID = (string)table.Rows[i]["GRO_ID"];
                    model.GRO_NAME = table.Rows[i]["GRO_NAME"]==System .DBNull .Value ?"":(string)table.Rows[i]["GRO_NAME"];
                    model.GRO_CONTESTS = table.Rows[i]["GRO_CONTESTS"]==System.DBNull .Value ?"":(string)table.Rows[i]["GRO_CONTESTS"];
                    model.GRO_AWARDS = table.Rows[i]["GRO_AWARDS"]==System .DBNull .Value ?"":(string)table.Rows[i]["GRO_AWARDS"];
                    model.GRO_SPECIALTY = table.Rows[i]["GRO_SPECIALTY"]==System .DBNull .Value ?0:(Int32)table.Rows[i]["GRO_SPECIALTY"];
                    model.GRO_IsShow = table.Rows[i]["GRO_IsShow"]==System .DBNull .Value ?true:(bool)table.Rows[i]["GRO_IsShow"];
                    model.AWA_NAME=table.Rows[i]["AWA_NAME"]==DBNull.Value?"":(string)table.Rows[i]["AWA_NAME"];
                    model.CON_NAME = table.Rows[i]["CON_NAME"] == DBNull.Value ? "" : (string)table.Rows[i]["CON_NAME"];
                    model.GRO_TEACHER = table.Rows[i]["GRO_TEACHER"] == DBNull.Value ? "" : (string)table.Rows[i]["GRO_TEACHER"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoGroups model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@GRO_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@GRO_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@GRO_CONTESTS",SqlDbType.NVarChar,50),
                new SqlParameter("@GRO_AWARDS",SqlDbType.NVarChar,50),
                new SqlParameter("@GRO_SPECIALTY",SqlDbType.Int,4),
                new SqlParameter("@GRO_IsShow",SqlDbType.Bit,2),
                new SqlParameter("@AWA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@CON_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@GRO_TEACHER",SqlDbType.NVarChar,50)
            };
            parameters[0].Value = model.GRO_ID;
            parameters[1].Value = model.GRO_NAME;
            parameters[2].Value = model.GRO_CONTESTS;
            parameters[3].Value = model.GRO_AWARDS;
            parameters[4].Value = model.GRO_SPECIALTY;
            parameters[5].Value = model.GRO_IsShow;
            parameters[6].Value = model.AWA_NAME;
            parameters[7].Value = model.CON_NAME;
            parameters[8].Value = model.GRO_TEACHER;
            return parameters;
        }
         /// <summary>
        /// 通过竞赛id以及小组名称获取视图的信息
        /// </summary>
        /// <param name="contestId">竞赛id</param>
        /// <param name="GroupName">小组名称</param>
        /// <returns></returns>
        public DataTable Getv_participantStuAndGroup(string contestId, string GroupName)
        {
            string sql = "select * from v_participantStuAndGroup where GRO_CONTESTS=@GRO_CONTESTS AND GRO_NAME=@GRO_NAME";
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@GRO_CONTESTS",contestId),
                new SqlParameter("@GRO_NAME",GroupName),
            };
            return dbhelper.ExcuteQuery(sql, parameter);
        }
    }
}